﻿using PADWH35;
using System;
using System.Collections.Generic;
using System.Data;
using System.Linq;
using System.Windows.Forms;

namespace PADWH35
{
    public partial class MaintainWORWorkOrderPAD : Form
    {
        List<string> a = new List<string>();
        DataTable d = new DataTable();
        public MaintainWORWorkOrderPAD()
        {
            InitializeComponent();
            //InitDateTimePicker(dateTimePicker1);
            AddCheckBox();
            InitDateTimePicker(dateTimePicker1);
            InitDateTimePicker(dateTimePicker2);
        }

        private void button1_Click(object sender, EventArgs e)
        {
            button4.Enabled = false;
            button2.Enabled = true;
            groupBox1.Text = "未显示工单列表";
            string jobNum = "";
            string orderNo = "";
            string jobType = "";
            string part = "";
            string[] arg = textBox1.Text.Split(new string[] { "\r\n" }, StringSplitOptions.RemoveEmptyEntries);
            if (arg.Count()>0)
            {
                jobNum = "(";
                foreach (string jN in arg)
                {
                    jobNum += $"'{jN}',";
                }
                jobNum = jobNum.Substring(0, jobNum.Length - 1);
                jobNum += ")";
            }

            var a = dateTimePicker1.CustomFormat;
            var b = dateTimePicker2.CustomFormat;
            DateTime f = new DateTime();
            if (a == " ")
            {
                f = new DateTime(1901, 01, 01, 00, 00, 00);
            }
            else
            {
                f = dateTimePicker1.Value.Date;
            }
            DateTime l = dateTimePicker2.Value.Date;
            getdata(jobNum, f, l);
        }

        public void getdata(string jobNum, DateTime first, DateTime last)
        {
            string sql = $@"SELECT 
                                   f.工单号 ,
                                   f.工单类型 ,                                  
                                   f.生产方式 ,
                                   f.订单号,
                                   f.物料编码 ,
                                   f.物料描述 ,
                                   f.创建日期 ,
                                   f.需求日期 ,
                                   f.开始日期 ,
                                   f.工单数量 ,
                                   CAST(MIN([配套比]) AS NVARCHAR)+'%'[配套比],
                                   f.已完成数量 ,
                                   f.已入库数量 ,
                                   f.工序 
                            FROM (
                            SELECT  a.JobNum [工单号] ,
                                    ( CASE a.ShortChar06
                                      WHEN N'Normal-常规工单' THEN N'常规工单'
                                      WHEN N'Re-work-返工工单' THEN N'返工工单'
                                      WHEN N'Mold Trial-试模工单' THEN N'试模工单'
                                      WHEN N'Mold Mfg(New)-新模工单' THEN N'新模工单'
                                      WHEN N'Mold Mfg(Modification)-改模工单' THEN N'改模工单'
                                      WHEN N'Mold Mfg(Repair)-修模工单' THEN N'修模工单'
                                      ELSE a.ShortChar06
                                      END ) [工单类型] ,
                                    a.PartNum [物料编码] ,
                                    ( CASE d.OrderNum
                                        WHEN '0' THEN N'备货生产'
                                        ELSE N'按单生产'
                                      END ) [生产方式] ,
                                    ( CASE d.OrderNum
                                        WHEN '0' THEN NULL
                                        ELSE d.OrderNum
                                      END ) [订单号] ,
                                    a.PartDescription [物料描述] ,
                                    a.CreateDate [创建日期] ,
                                    a.ReqDueDate [需求日期] ,
                                    a.StartDate [开始日期] ,
                                    (SELECT CAST(a.ProdQty AS int)) [工单数量] ,
                                    (CASE WHEN e.RequiredQty<>0 THEN CAST((e.IssuedQty/e.RequiredQty)*100 AS INT) ELSE 0 END) [配套比],
                                    (SELECT CAST(a.QtyCompleted AS int))[已完成数量],
                                    (SELECT CAST(d.ReceivedQty AS int)) [已入库数量],
                                    b.OpDtlDesc [工序] 
                            FROM    JobHead a
                                    LEFT JOIN ( SELECT  Company ,
                                                        JobNum ,
                                                        ResourceGrpID ,
                                                        OpDtlDesc
                                                FROM    Erp.JobOpDtl
                                                GROUP BY Company ,
                                                        JobNum ,
                                                        ResourceGrpID ,
                                                        OpDtlDesc
                                              ) b ON b.Company = a.Company
                                                     AND b.JobNum = a.JobNum
                                    LEFT JOIN ResourceGroup c ON b.Company = c.Company
                                                                 AND b.ResourceGrpID = c.ResourceGrpID
                                    LEFT JOIN Erp.JobProd d ON a.Company = d.Company
                                                               AND a.JobNum = d.JobNum
                                    LEFT JOIN JobMtl e ON a.JobNum= e.JobNum AND a.Company=e.Company
                            WHERE   c.JCDept = 'WOR'
                                    AND a.Company = 'NSZ'
                                    AND a.CreateDate between '{first}' and '{last}'
                                    AND a.JobClosed = 0 ";

            if (!string.IsNullOrEmpty(jobNum))
            {
                sql += $" AND a.JobNum in  {jobNum}";
            }

            sql += @"             ) f
            GROUP BY
                   f.工单号 ,
                   f.工单类型 ,
                   f.物料编码 ,
                   f.生产方式 ,
                   f.订单号 ,
                   f.物料描述 ,
                   f.创建日期 ,
                   f.需求日期 ,
                   f.开始日期 ,
                   f.工单数量 ,
                   f.已完成数量 ,
                   f.已入库数量 ,
                   f.工序
             ORDER BY f.需求日期
            ";
            DataTable dt = DBHelper.ExecuteDt(sql);
            d = dt.Clone();
            foreach (DataRow t in dt.Rows)
            {
                if (!a.Contains(t[0].ToString()))
                {
                    d.Rows.Add(t.ItemArray);
                }
            }
            dataGridView1.DataSource = d;
            //setFirstCheckBoxValue(d);
            dataGridView1.Columns["配套比"].Visible = false;
        }

        /// <summary>
        /// 设置勾选框
        /// </summary>
        /// <param name="dt"></param>
        private void setFirstCheckBoxValue(DataTable dt)
        {
            for (int i = 0; i < dt.Rows.Count; i++)
            {
                if (a.Contains(dt.Rows[i]["工单号"].ToString()))
                {
                    dataGridView1.Rows[i].Cells["cb_check"].Value = true;
                }
            }
        }
        public void AddCheckBox()
        {
            DataGridViewCheckBoxColumn columncb = new DataGridViewCheckBoxColumn();
            columncb.HeaderText = "选择";
            columncb.SortMode = DataGridViewColumnSortMode.Programmatic;
            columncb.Name = "cb_check";
            columncb.TrueValue = true;
            columncb.FalseValue = false;
            columncb.DataPropertyName = "IsChecked";
            dataGridView1.Columns.Insert(0, columncb);
        }

        private void button2_Click(object sender, EventArgs e)
        {
            //List<string> jobNums = new List<string>();
            //List<string> deletes = new List<string>();
            List<string> inserts = new List<string>();
            foreach (DataGridViewRow row in dataGridView1.Rows)
            {
                if (row.Cells["cb_check"].Value != null)
                {
                    if (Convert.ToBoolean(row.Cells["cb_check"].Value))
                    {
                        inserts.Add(row.Cells[1].Value.ToString());
                    }
                }
            }           
            if (inserts.Count > 0)
            {
                insert(inserts);
            }

            MessageBox.Show("添加成功!");
            a = getJobNum();
            button1_Click(null,null);        
        }
        private static List<string> getJobNum()
        {
            string sql = " SELECT Key1 FROM ice.ud21 WHERE Company = 'NSZ' AND Key5 = 'JobNumWorkOrder'";
            var dt = DBHelper.ExecuteDt(sql);
            List<string> jobNum = new List<string>();
            foreach (DataRow row in dt.Rows)
            {
                jobNum.Add(row["key1"].ToString());
            }
            return jobNum;
        }
        /// <summary>
        /// 添加
        /// </summary>
        /// <param name="jobNum"></param>
        public static void insert(List<string> jobNum)
        {
            try
            {
                foreach (string jobnum in jobNum)
                {
                    string sql = $" INSERT INTO ice.ud21 (Company,Key1,key2,Key5) VALUES('NSZ','{jobnum}',CONVERT(varchar(100), GETDATE(), 20),'JobNumWorkOrder')";
                    DBHelper.ExecuteSql(sql);
                }

            }
            catch (Exception ex)
            {
                MessageBox.Show(ex.ToString());
            }
        }

        /// <summary>
        /// 删除
        /// </summary>
        /// <param name="jobNum"></param>
        public static void delete(List<string> jobNum)
        {

            try
            {
                foreach (string jobnum in jobNum)
                {
                    string sql = $" DELETE FROM ice.UD21 WHERE Company = 'NSZ' AND Key1 = '{jobnum}' AND Key5 = 'JobNumWorkOrder'";
                    DBHelper.ExecuteSql(sql);
                }
            }
            catch (Exception ex)
            {
                MessageBox.Show(ex.ToString());
            }
        }
        private void Form1_Load(object sender, EventArgs e)
        {
            a = getJobNum();
            button4.Enabled = false;
        }

        private void checkBox1_CheckedChanged(object sender, EventArgs e)
        {

        }

        private void checkBox2_CheckedChanged(object sender, EventArgs e)
        {

        }

        private void dataGridView1_ColumnHeaderMouseClick(object sender, DataGridViewCellMouseEventArgs e)
        {
            setFirstCheckBoxValue(d);
        }

        public static void InitDateTimePicker(DateTimePicker dtp)
        {
            dtp.Format = DateTimePickerFormat.Custom;
            dtp.CustomFormat = " ";  //必须设置成" "
            dtp.ValueChanged -= DateTimePicker_ValueChanged;
            dtp.ValueChanged += DateTimePicker_ValueChanged;
            dtp.KeyPress -= DateTimePicker_KeyPress;
            dtp.KeyPress += DateTimePicker_KeyPress;
        }

        public static void DateTimePicker_ValueChanged(object sender, EventArgs e)
        {
            DateTimePicker dtp = (DateTimePicker)sender;
            dtp.Format = DateTimePickerFormat.Long;
            dtp.CustomFormat = null; //null;
            dtp.Checked = false;// 解决BUG ：防止日期控件不能选择相同日期的 --- 要放置在设置格式之后
        }

        public static void DateTimePicker_KeyPress(object sender, KeyPressEventArgs e)
        {
            if (e.KeyChar == (char)8)  // backspace左删除键
            {
                DateTimePicker dtp = (DateTimePicker)sender;
                dtp.Format = DateTimePickerFormat.Custom;
                dtp.CustomFormat = " ";
            }
        }

        private void button3_Click(object sender, EventArgs e)
        {
            getShownDate();
        }
        private void getShownDate()
        {
            button2.Enabled = false;
            button4.Enabled = true;
            groupBox1.Text = "已显示工单列表";
            textBox1.Text = "";
            InitDateTimePicker(dateTimePicker1);
            InitDateTimePicker(dateTimePicker2);
            string sql = $@"SELECT 
                                   f.工单号 ,
                                   f.工单类型 ,                                  
                                   f.生产方式 ,
                                   f.订单号,
                                   f.物料编码 ,
                                   f.物料描述 ,
                                   f.创建日期 ,
                                   f.需求日期 ,
                                   f.开始日期 ,
                                   f.工单数量 ,
                                   CAST(MIN([配套比]) AS NVARCHAR)+'%'[配套比],
                                   f.已完成数量 ,
                                   f.已入库数量 ,
                                   f.工序 
                            FROM (
                            SELECT  a.JobNum [工单号] ,
                                    ( CASE a.ShortChar06
                                      WHEN N'Normal-常规工单' THEN N'常规工单'
                                      WHEN N'Re-work-返工工单' THEN N'返工工单'
                                      WHEN N'Mold Trial-试模工单' THEN N'试模工单'
                                      WHEN N'Mold Mfg(New)-新模工单' THEN N'新模工单'
                                      WHEN N'Mold Mfg(Modification)-改模工单' THEN N'改模工单'
                                      WHEN N'Mold Mfg(Repair)-修模工单' THEN N'修模工单'
                                      ELSE a.ShortChar06
                                      END ) [工单类型] ,
                                    a.PartNum [物料编码] ,
                                    ( CASE d.OrderNum
                                        WHEN '0' THEN N'备货生产'
                                        ELSE N'按单生产'
                                      END ) [生产方式] ,
                                    ( CASE d.OrderNum
                                        WHEN '0' THEN NULL
                                        ELSE d.OrderNum
                                      END ) [订单号] ,
                                    a.PartDescription [物料描述] ,
                                    a.CreateDate [创建日期] ,
                                    a.ReqDueDate [需求日期] ,
                                    a.StartDate [开始日期] ,
                                    (SELECT CAST(a.ProdQty AS int)) [工单数量] ,
                                    (CASE WHEN e.RequiredQty<>0 THEN CAST((e.IssuedQty/e.RequiredQty)*100 AS INT) ELSE 0 END) [配套比],
                                    (SELECT CAST(a.QtyCompleted AS int))[已完成数量],
                                    (SELECT CAST(d.ReceivedQty AS int)) [已入库数量],
                                    b.OpDtlDesc [工序] 
                            FROM    JobHead a
                                    LEFT JOIN ( SELECT  Company ,
                                                        JobNum ,
                                                        ResourceGrpID ,
                                                        OpDtlDesc
                                                FROM    Erp.JobOpDtl
                                                GROUP BY Company ,
                                                        JobNum ,
                                                        ResourceGrpID ,
                                                        OpDtlDesc
                                              ) b ON b.Company = a.Company
                                                     AND b.JobNum = a.JobNum
                                    LEFT JOIN ResourceGroup c ON b.Company = c.Company
                                                                 AND b.ResourceGrpID = c.ResourceGrpID
                                    LEFT JOIN Erp.JobProd d ON a.Company = d.Company
                                                               AND a.JobNum = d.JobNum
                                    LEFT JOIN JobMtl e ON a.JobNum= e.JobNum AND a.Company=e.Company
                            WHERE   c.JCDept = 'WOR'
                                    AND a.Company = 'NSZ'                                
                                    AND a.JobClosed = 0 ";

            sql += @"             ) f
            GROUP BY
                   f.工单号 ,
                   f.工单类型 ,
                   f.物料编码 ,
                   f.生产方式 ,
                   f.订单号 ,
                   f.物料描述 ,
                   f.创建日期 ,
                   f.需求日期 ,
                   f.开始日期 ,
                   f.工单数量 ,
                   f.已完成数量 ,
                   f.已入库数量 ,
                   f.工序
             ORDER BY f.需求日期
            ";
            DataTable dt = DBHelper.ExecuteDt(sql);
            d = dt.Clone();
            foreach (DataRow t in dt.Rows)
            {
                if (a.Contains(t[0].ToString()))
                {
                    d.Rows.Add(t.ItemArray);
                }
            }
            dataGridView1.DataSource = d;
            //setFirstCheckBoxValue(d);
            dataGridView1.Columns["配套比"].Visible = false;
        }
        private void button4_Click(object sender, EventArgs e)
        {
            List<string> deletes = new List<string>();
            foreach (DataGridViewRow row in dataGridView1.Rows)
            {
                if (row.Cells["cb_check"].Value != null)
                {
                    if (Convert.ToBoolean(row.Cells["cb_check"].Value))
                    {
                        deletes.Add(row.Cells[1].Value.ToString());
                    }
                }
            }
            if (deletes.Count > 0)
            {
                delete(deletes);
            }
            MessageBox.Show("移除成功!");
            a = getJobNum();
            getShownDate();
        }
        //public static void InitDateTimePicker(DateTimePicker dtp)
        //{
        //    dtp.Format = DateTimePickerFormat.Custom;
        //    dtp.CustomFormat = " ";  //必须设置成" "
        //    dtp.ValueChanged -= DateTimePicker_ValueChanged;
        //    dtp.ValueChanged += DateTimePicker_ValueChanged;
        //    dtp.KeyPress -= DateTimePicker_KeyPress;
        //    dtp.KeyPress += DateTimePicker_KeyPress;
        //}

        //public static void DateTimePicker_ValueChanged(object sender, EventArgs e)
        //{
        //    DateTimePicker dtp = (DateTimePicker)sender;
        //    dtp.Format = DateTimePickerFormat.Long;
        //    dtp.CustomFormat = null; //null;
        //    dtp.Checked = false;// 解决BUG ：防止日期控件不能选择相同日期的 --- 要放置在设置格式之后
        //}

        //public static void DateTimePicker_KeyPress(object sender, KeyPressEventArgs e)
        //{
        //    if (e.KeyChar == (char)8)  // backspace左删除键
        //    {
        //        DateTimePicker dtp = (DateTimePicker)sender;
        //        dtp.Format = DateTimePickerFormat.Custom;
        //        dtp.CustomFormat = " ";
        //    }
        //}
    }
}
